﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mengsk.ERP.Database;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Mengsk.ERP.Orders
{
    public class OrderManager
    {
        private static OrderManager instance = new OrderManager();

        public static OrderManager Instance { get { return instance; } }

        public const string TABLE_ORDER = "Order";
        public const string TABLE_ORDERITEM = "OrderItem";
        public const string TABLE_ORDERREFUSED = "OrderRefused";


        public Order[] GetOrdersByDate(DateTime date)
        {
            string where = string.Format("CAST (CreateTime as date) ='{0}' and [type]=2 and LEN(deliverycompany)>0 and state<>'STATE_COD_CANCEL'", date.ToString("yyyy-MM-dd"));
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, where, null, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByDeliveryNumber(string deliveryNumber)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "deliverynumber=@deliverynumber", new SqlParameter[] { new SqlParameter("@deliverynumber", deliveryNumber) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }

            return orders;
        }

        public Order[] GetOrdersByPopId(string popId)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "POPID=@POPID", new SqlParameter[] { new SqlParameter("@POPID", popId) }, false, "Items");
            return orders;
        }

        public Order[] GetOrdersWithToSend()
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "State='STATE_COD_WAIT_SHIP'", null, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByName(string name)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "name=@name", new SqlParameter[] { new SqlParameter("@name", name) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByPhoneOrMobile(string phoneOrMobile)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "phone=@phone OR phonemobile=@phonemobile", new SqlParameter[] { new SqlParameter("@phone", phoneOrMobile), new SqlParameter("@phonemobile", phoneOrMobile) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByNameAndPopIDNotEqual(string name, string popID)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "name=@name and popid<>@popid", new SqlParameter[] { new SqlParameter("@name", name), new SqlParameter("@popid", popID) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByPhoneOrMobileAndPopIDNotEqual(string phoneOrMobile, string popID)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "(phone=@phone OR phonemobile=@phonemobile) and popid<>@popid", new SqlParameter[] { new SqlParameter("@phone", phoneOrMobile), new SqlParameter("@phonemobile", phoneOrMobile), new SqlParameter("@popid", popID) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public Order[] GetOrdersByRealNameAndAddressAndPopIDNotEqual(string realName, string address, string popid)
        {
            Order[] orders = DBHelper.Instance.Select<Order>("", TABLE_ORDER, "(RealName=@RealName And Address=@Address) and popid<>@popid", new SqlParameter[] { new SqlParameter("@RealName", realName), new SqlParameter("@Address", address), new SqlParameter("@popid", popid) }, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }
            return orders;
        }

        public int DeleteOrderRefuesedByDeliveryNumberAndCompany(string deliveryCompany, string deliveryNumer)
        {
            return DBHelper.Instance.Delete("", TABLE_ORDERREFUSED, "DeliveryCompany=@DC and DeliveryNumber=@DN", new SqlParameter("@DC", deliveryCompany), new SqlParameter("@DN", deliveryNumer));
        }

        public void InsertOrderRefuseds(OrderRefused[] orderRefuses)
        {
            int ret = 0;

            foreach (var item in orderRefuses)
            {
                ret += DBHelper.Instance.Insert("", TABLE_ORDERREFUSED, item, false);
            }
        }

        public Order[] GetOrdersByQuery(string qq, string realName, string deliveryNumber, string phone, string number, string color, string size, DateTime startTime, string productName, string address, string comment)
        {
            string query = "select [Order].* from [Order] inner join [OrderItem] on [Order].ERPID=[OrderItem].ERPID where ";
            bool append = false;

            if (string.IsNullOrWhiteSpace(qq) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += "[Order].[Name]='" + qq + "'";
            }

            if (string.IsNullOrWhiteSpace(realName) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;

                query += "[Order].[RealName] like '%" + realName + "%'";
            }

            if (string.IsNullOrWhiteSpace(deliveryNumber) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += "[Order].[DeliveryNumber] like '%" + deliveryNumber + "%'";
            }

            if (string.IsNullOrWhiteSpace(phone) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += string.Format("([Phone] like '%{0}%' or [PhoneMobile] like '%{1}%')", phone, phone);
            }

            if (string.IsNullOrWhiteSpace(number) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += "[OrderItem].[InternalName] like '%" + number + "%'";
            }

            if (string.IsNullOrWhiteSpace(color) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += "[OrderItem].[Color] like '%" + color + "%'";
            }

            if (string.IsNullOrWhiteSpace(size) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += "[OrderItem].[Size] like '%" + size + "%'";
            }

            if (startTime != DateTime.MinValue)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += string.Format("[Order].CreateTime>='{0}'", startTime.ToString("yyyy-MM-dd HH:dd:ss"));
            }

            if (string.IsNullOrWhiteSpace(productName) == false)
            {
                if (append == true)
                {
                    query += " and ";
                }
                append = true;
                query += string.Format("[OrderItem].[Name] like '%{0}%'", productName);
            }

            if (string.IsNullOrWhiteSpace(address) == false)
            {
                string[] addresses = address.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                foreach (var ad in addresses)
                {
                    if (append == true)
                    {
                        query += " and ";
                    }
                    append = true;
                    query += string.Format("[Order].[Address] like '%{0}%'", ad);
                }
            }

            if (string.IsNullOrWhiteSpace(comment) == false)
            {
                string[] commentes = comment.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                foreach (var com in commentes)
                {
                    if (append == true)
                    {
                        query += " and ";
                    }
                    append = true;
                    query += string.Format("[Order].[Comment] like '%{0}%'", com);
                }
            }

            if (append == false)
            {
                throw new Exception("请输入查询条件");
            }

            Order[] orders = DBHelper.Instance.SelectEx<Order>("", query, null, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }

            return orders;
        }

        public Order[] GetOrdersByQuery2(string query)
        {
            Order[] orders = DBHelper.Instance.SelectEx<Order>("", query, null, false, "Items");
            foreach (var order in orders)
            {
                var items = DBHelper.Instance.Select<OrderItem>("", TABLE_ORDERITEM, "ERPID='" + order.ERPID + "'", null, false);
                order.Items.AddRange(items);
            }

            return orders;
        }
    }
}
